﻿using System.Data;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO.Packaging;
using System.Linq;
using System.Security.Principal;
using System.Threading;
using System.Web;
using System.Windows.Forms;
using System.Web.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using iTextSharp.text.pdf.parser;
using Microsoft.Office.Interop.Excel;
using System.Web.Helpers;
using MathNet.Numerics.LinearAlgebra.Solvers;
using NPOI.SS.Formula.Eval;
using NPOI.XWPF.UserModel;
using Kingdee.BOS.Util;
using Microsoft.WindowsAPICodePack.Dialogs;

namespace LanDBom.Models.Algorithm
{
    public class GY_Algorithm
    {
        LDbaseEntities db = new LDbaseEntities();
        JChelp chelp = new JChelp();
        /*//计算工艺价格(水印纸箱)
        internal double Jiaoge(operational operat, string bOM2_id)
        {
            var bomin = db.BOMini.Find(int.Parse(bOM2_id));
            //获取物料的规格
            string Guige = bomin.BOM2_Guige;
            double Pro = Algorithm(ref Guige);//计算的平方面积--纸版
            var DPro = Algor(ref Guige);//计算的立方面积--吸塑
            var HandG = AlgorHG(ref Guige);//计算结合费用---结合
            //转换
            //计算加工费用 获取加工费 查询library
            double Y_price = 0.000;///印刷价格
            var shun = db.libray.FirstOrDefault(s => s.LB_fne == operat.OP_Yin);//获取印刷的标签
            if (shun != null)
            {
                if (shun.LB_fne == "满版印刷")
                {
                    string Y = operat.OP_YinSe;
                    int A = HuoQuZF(Y);//获取使用了几色
                    var jiaed = double.Parse(shun.LB_jia.ToString());
                    Y_price = jiaed * Pro;
                }
                else
                {
                    string Y = operat.OP_YinSe;
                    int A = HuoQuZF(Y);//获取使用了几色
                    double inse = double.Parse(shun.LB_jia.ToString());//获取价格
                    Y_price = (A * inse) / 1000;//使用旧的计算方式 千张/色
                }
            }
            double M_price = 0;//模切价格
            if (operat.OP_Muqie != null)
            {
                var da = db.libray.Where(s => s.LB_fne == "模切").FirstOrDefault();
                M_price = double.Parse(da.LB_jia.ToString()) * double.Parse(operat.OP_Muqie);
            }
            double X_pricesed = 0;//--熏蒸
            if (operat.OP_Muxinag != null)
            {
                //查询数据库是否有该项
                var sun = db.libray.Where(s => s.LB_fne == operat.OP_Muxinag).FirstOrDefault();
                var X_prices = sun == null ? "0" : sun.LB_jia.ToString();
                X_pricesed = int.Parse(X_prices) * Pro;
            }
            double B_price = 0;//表面处理 面积*价格
            if (operat.OP_biao != null)
            {
                //考虑多种情况
                string[] highlight = Syracuse(operat.OP_biao);
                var X_prices = 0.0;
                for (int i = 0; i < highlight.Length; i++)
                {
                    var highl = highlight[i].ToString();
                    var sun = db.libray.Where(s => s.LB_fne == highl).FirstOrDefault();
                    var X_pr = double.Parse(sun.LB_jia.ToString());
                    X_prices += X_pr * Pro;
                }
                B_price = X_prices;//总价
            }
            //吸塑计算  默认为克重
            double xishu = 0.0;
            if (operat.OP_shu != null)
            {
                //考虑多种情况
                string[] highlight = Syracuse(operat.OP_shu);
                for (int i = 0; i < highlight.Length; i++)
                {
                    var highl = highlight[i].ToString();
                    var sun = db.libray.Where(s => s.LB_fne == highl).FirstOrDefault();
                    var X_pr = double.Parse(sun.LB_jia.ToString());//?/KG
                    xishu += X_pr * Pro * 0.92;
                }
            }
            //结合计算  默认为米
            double jihe = 0.0;
            if (operat.OP_jiehe != null)
            {
                //考虑多种情况
                string[] highlight = Syracuse(operat.OP_jiehe);
                for (int i = 0; i < highlight.Length; i++)
                {
                    var highl = highlight[i].ToString();
                    var sun = db.libray.Where(s => s.LB_fne == highl).FirstOrDefault();
                    var X_pr = sun == null ? 0.0 : double.Parse(sun.LB_jia.ToString());//  X/米
                    jihe += X_pr * HandG;
                }
            }
            //总价计算 根据分类进行计算
            double Z_price = 0;
            int MUqiessed = operat.OP_Muqie == null ? 1 : int.Parse(operat.OP_Muqie);
            var MUqiess = 0;
            if (MUqiessed != 1)
            {
                MUqiess += 1;
            }
            var shjnf = chelp.Shungdint(bOM2_id);

            Z_price = (Y_price + M_price + B_price + jihe) / MUqiess;//计算的是材料切了几刀中其中一块材料的加工费

            return Z_price;//
        }*/
        //字符串转换 计算面积
        private static double Algorithm(ref string Guige)
        {
            double Pro = 1;
            if (Guige != null)
            {
                Guige = Guige.Replace("mm", "");
                string[] Guiges = Guige.Split('*');
                double[] ints = new double[Guiges.Length];
                int k = 0;//用于存储为空时的数值
                for (int i = 0; i < Guiges.Length; i++)
                {
                    if (Guiges[i] != "")
                    {
                        ints[i] = double.Parse(Guiges[i]);
                    }
                    else
                    {
                        k++;
                    }

                }
                //计算面积 长*宽
                if (k == 0)
                {
                    for (int i = 0; i < ints.Length - 1; i++)
                    {
                        Pro *= ints[i];
                    }
                }
                else
                {
                    for (int i = 0; i < ints.Length - k; i++)
                    {
                        Pro *= ints[i];
                    }
                }
            }
            var zhusn = Pro / 1000000;//平方毫米===》平方米
            return zhusn;
        }
        //字符串转换 计算体积
        private static double Algor(ref string Guige)
        {
            double Pro = 1;
            if (Guige != null)
            {
                Guige = Guige.Replace("mm", "");
                string[] Guiges = Guige.Split('*');
                if (Guiges.Length > 2)
                {
                    for (int i = 0; i < Guiges.Length; i++)
                    {
                        if (Guiges[i] != "")
                        {
                            var hunsed = double.Parse(Guiges[i]) / 1000;
                            Pro *= hunsed;
                        }
                    }
                }

            }
            return Pro;//立方米
        }
        //字符串转换 计算长加高
        private static double AlgorHG(ref string Guige)
        {
            double Pro = 1;
            if (Guige != null)
            {
                Guige = Guige.Replace("mm", "");
                string[] Guiges = Guige.Split('*');
                //判断是否有高的值
                if (Guiges.Length > 2)
                {
                    var Long = double.Parse(Guiges[0].ToString());
                    var Gao = double.Parse(Guiges[Guiges.Length - 1].ToString());
                    Pro = (Long + Gao) / 1000;//转换成米
                }
                else
                {
                    Pro = 0;
                }
            }
            return Pro;//立方米
        }
        //计算实际个数（排除NULL或“”的情况）
        private static int HuoQuZF(string Y)
        {
            string[] S = Syracuse(Y);
            string[] C = new string[S.Length];
            int sun = 0;
            for (int i = 0; i < S.Length; i++)
            {
                if (S[i] != "")
                {
                    C[i] = S[i];
                }
                else
                {
                    sun++;
                }
            }
            int A = C.Length - sun;
            return A;
        }
        //分解字符串（带“；”的）
        private static string[] Syracuse(string Y)
        {
            return Y.Split(';');
        }


        //计算BOM价格(纸箱)
        public double Tprice(List<BOMini> bOs)
        {
            double Tprice = 0.00;
            /*一，遍历数组进行分组  根据标签进行分组*/
            for (int i = 0; i < bOs.Count; i++)
            {
                if ((bOs[i].BOM2_chen == "Leve0"))
                {
                    var hu = bOs[i].BOM2_BQing;//获取leve0 的标签从而获取BOM的组合
                    double hus = Leve2_price(bOs, hu);//leve1层级的总价

                    /*leve2的总值=leve2的物料成本+工艺*用量
                     * leve1=（leve2总体+自身工艺+）*用量
                     * leve0= leve1的总价+自身工艺）*用量 
                     */
                    var gong = bOs[i].BOM2_Gong;
                    var huse = db.operational.FirstOrDefault(s => s.OP_ID == gong);//是否存在工艺
                    var HH = huse == null ? "0.0" : huse.OP_price==null?"0.0" : huse.OP_price.ToString();
                    var hun = huse == null ? 0.0 : double.Parse(HH);
                    var yong = double.Parse(bOs[i].BOM2_Yong);
                    var DEiijj = bOs[i].BOM2_liao;
                    var shun = db.Formation.FirstOrDefault(s => s.FM_bai == DEiijj);
                    var ZiSeh = 0.0;
                    if (shun != null)
                    {
                        ZiSeh = shun.FM_price == null ? 0.0 : (double)shun.FM_price;
                    }
                    Tprice = (hus + hun );
                    string dd = Tprice.ToString();
                    //将价格保存到数据库
                    var ud = bOs[i].BOM2_id;
                    var shunji = db.BOMini.FirstOrDefault(s => s.BOM2_id == ud);
                    shunji.BOM2_mioa = Tprice.ToString();
                    shunji.BOM2_CB = Tprice.ToString();
                    db.Entry<BOMini>(shunji).State=System.Data.Entity.EntityState.Modified;
                    int row = db.SaveChanges();

                    //var shunji = db.BOMini.FirstOrDefault(s => s.BOM2_id == ud);
                    //shunji.BOM2_mioa = Tprice.ToString();
                    //shunji.BOM2_CB = Tprice.ToString();
                    //db.Entry(shunji).State=System.Data.Entity.EntityState.Modified;
                    
                    Tprice = 0.0;
                }
            }
            return Tprice;
        }

        private double Leve2_price(List<BOMini> bOs, string hu)
        {
            //筛查bos中的符合要的列
            bOs = bOs.Where(s => s.BOM2_BQing.Contains(hu)).ToList();//符合要求的列

            double sum = 0.0;//记录总数
                             //根据列的内容进行统计
            for (int i = 0; i < bOs.Count; i++)
            {
                if (bOs[i].BOM2_chen == "leve1")
                {
                    var A = bOs[i].BOM2_BQing;//获取当前leve1 的标签根据标签查找leve2 的值
                    double L_sum = 0.00;//记录leve1的值
                    for (int j = 0; j < bOs.Count; j++)
                    {
                        if (bOs[j].BOM2_BQing == A + "-a")
                        {
                            //当数值为空时的设置
                            var G1 = bOs[j].BOM2_Yong != null ? 1 : int.Parse(bOs[j].BOM2_Yong);//该物料的用量
                            var S1 = bOs[j].BOM2_Gong;//该leve2的工艺id
                            var A1 = bOs[j].BOM2_liao;//物料id
                            var shunf = bOs[j].Fo_id;
                            var U1 = db.operational.Where(s => s.OP_ID == S1).FirstOrDefault();   //工艺id的列
                            var B = db.Ndardo.Where(s => s.Fo_id == A1).FirstOrDefault();//物料价格标准库
                            double H = 0.0;//物料的价格(核价表无物料是使用物料表)
                            if (B == null)
                            {
                                var hij = db.Formation.FirstOrDefault(s => s.FM_ID == shunf);//物料库价格
                                if (hij != null && !string.IsNullOrEmpty(hij.FM_price.ToString()))
                                {
                                    try
                                    {
                                        H = double.Parse(hij.FM_price.ToString());
                                    }
                                    catch (FormatException)
                                    {
                                        // 转换失败，处理异常，例如设置H为默认值或记录错误信息。  
                                        H = 0.0; // 或其他默认值  
                                    }
                                }
                                else
                                {
                                    H = 0.0; // 或其他默认值  
                                }
                            }
                            else
                            {
                                H = double.Parse(B.Nd_jiage.ToString());//核价表的物料的价格
                            }
                            var GY = 0.0;
                            if (U1 != null)
                            {
                                var sji = U1.OP_price == null ? "0.0" : U1.OP_price.ToString();
                                GY = double.Parse(sji.ToString());//物料的工艺价格
                            }
                            //判断物料属性
                            var bomhudn = bOs[j].BOM2_id.ToString();
                            var shund = chelp.Shungdint(bomhudn);//获取分类的id
                            var Guiges = bOs[j].BOM2_Guige;//获取物料的规格
                            var Tji = Algor(ref Guiges);//计算体积
                            double Pro = Algorithm(ref Guiges);//计算的面积
                                                               //物料的价格：leve2：leve2的单价；leve1：Leve2*用量 leve0：leve1*用量
                                                               //将价格保存到数据库
                            var bomid = bOs[j].BOM2_id;
                            var shunji = db.BOMini.FirstOrDefault(s => s.BOM2_id == bomid);
                            shunji.BOM2_mioa = GY.ToString();
                            shunji.BOM2_CB = GY.ToString();
                            db.Entry(shunji).State=System.Data.Entity.EntityState.Modified;
                            int row2 = db.SaveChanges();
                            L_sum += GY * G1; //leve1的单价====leve2成的所有物料的单价*自身用量的总和
                        }
                    }
                    var P1 = (bOs[i].BOM2_Yong != null) ? int.Parse(bOs[i].BOM2_Yong) : 0;//leve1的用量
                    var gui = bOs[i].BOM2_Guige;
                    var Msun = Algorithm(ref gui);//leve1的表面积（平方）
                    var GT = bOs[i].BOM2_Gong;//获取工艺的价格
                    var HUJ = bOs[i].BOM2_liao;//物料id
                    var shshn = bOs[i].Fo_id;
                    var GH = db.operational.FirstOrDefault(s => s.OP_ID == GT);//工艺id的列
                    var B1 = db.Ndardo.Where(s => s.Fo_id == HUJ).FirstOrDefault();//物料价格标准库
                    var PR = 0.0;
                    if (GH != null)
                    {
                      var sji=  GH.OP_price == null ? "0.0" : GH.OP_price.ToString();
                        PR = double.Parse(sji.ToString());
                    }
                    var sjiddko = (L_sum + PR );

                    var JIid = bOs[i].BOM2_id;
                    var leves = db.BOMini.FirstOrDefault(s => s.BOM2_id == JIid);
                    leves.BOM2_mioa = sjiddko.ToString();
                    leves.BOM2_CB = sjiddko.ToString();
                    db.Entry(leves).State = System.Data.Entity.EntityState.Modified;
                    int row = db.SaveChanges();
                    sum += (L_sum + PR ) * P1;//计算公式：（leve2的总价+leve1的工艺价格+leve1的自身价格）*物料的面积）*用量
                }
            }
            return sum;//leve1层级的总价
        }



        //获取文件夹地址
        public string SelectPath()
        {
            //文件夹开始
            CommonOpenFileDialog dialog = new CommonOpenFileDialog("请选择一个文件夹");
            dialog.IsFolderPicker = true;
            dialog.AllowNonFileSystemItems = true;
            dialog.Multiselect = true;

            var path = "";
            Thread t = new Thread((ThreadStart)(() =>
            {

                if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
                {
                    //获取文件夹路径
                    string foldPath = dialog.FileName;
                    path = foldPath;
                }
            }
                ));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            t.Join();

            return path;
        }
        //获取文件地址
        public string SelectwenPath()
        {
            //文件夹开始
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = false;//是否多选
            dialog.Title = "请选择文件路径";
            dialog.Filter = "所有文件(*.*)|*.*";
            var path = "";
            Thread t = new Thread((ThreadStart)(() =>
            {

                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    //获取文件夹路径
                    string foldPath = dialog.FileName;
                    path = foldPath;
                }
            }
                ));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            t.Join();
            return path;
        }
        /*//获取文件地址
        public string SelectwenPathADD()
        {
            //文件夹开始
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = false;//是否多选
            dialog.Title = "请选择文件路径";
            dialog.Filter = "所有文件(*.*)|*.*";
            var path = "";
           
            Thread t = new Thread((ThreadStart)(() =>
            {

                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    //获取文件夹路径
                    string foldPath = dialog.FileName;
                    path = foldPath;
                }
            }
                ));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            t.Join();
            

            return path;
        }*/
        //获取Excel文件地址
        public System.Data.DataTable ExcelwenPath()
        {
            //文件夹开始
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = false;//是否多选
            dialog.Title = "请选择文件路径";
            dialog.Filter = "Excel文件(*.xlsx*)|*.xlsx*|Excel文件(*.xls*)|*.xls*";
            var data = new System.Data.DataTable();
            Thread t = new Thread((ThreadStart)(() =>
            {

                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    //获取文件夹路径
                    string foldPath = dialog.FileName;
                    data = ExcelToDatatable(foldPath, "Sheet1", true);
                }
            }
                 ));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            t.Join();

            return data;
        }
        //体积计算公式


        //修改下载路径方法
        public Dengli Getyuangong(int Fo_id)
        {
            LDbaseEntities LD = new LDbaseEntities();
            Dengli nda = LD.Dengli.SingleOrDefault(x => x.DE_ID == Fo_id);

            return nda;
        }

        public int Change_Download_path(Dengli nda)
        {
            LDbaseEntities LD = new LDbaseEntities();

            Dengli Nda = Getyuangong(int.Parse(nda.DE_ID.ToString()));

            Nda.DE_ID = nda.DE_ID;
            Nda.DE_dizhi = nda.DE_dizhi;

            return LD.SaveChanges();
        }


        #region 读取Excel数据
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名，true是</param>
        /// <returns>返回的DataTable</returns>
        public static System.Data.DataTable ExcelToDatatable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            int cellCount = 0;//列数
            int rowCount = 0;//行数
            try
            {
                using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    // 创建HSSFWorkbook或XSSFWorkbook对象，取决于你的Excel文件格式  
                    IWorkbook workbook;
                    if (System.IO.Path.GetExtension(fileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }

                    // 获取第一个工作表  
                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        cellCount = firstRow.LastCellNum; //第一行最后一个cell的编号 即总的列数
                        if (isFirstRowColumn)//如果第一行是标题行
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)//第一行列数循环
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);//获取标题
                                data.Columns.Add(column);//添加列
                            }
                            startRow = sheet.FirstRowNum + 2;//1（即第二行，第一行0从开始）
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;//0
                        }
                        //最后一行的标号
                        rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)//循环遍历所有行
                        {
                            IRow row = sheet.GetRow(i);//第几行
                            if (row != null)
                            {
                                //将excel表每一行的数据添加到datatable的行中
                                DataRow dataRow = data.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    {
                                        dataRow[j] = row.GetCell(j).ToString();
                                    }
                                }
                                data.Rows.Add(dataRow);
                            }
                            else
                            {
                                continue; //没有数据的行默认是null;
                            }

                        }
                    }
                    return data;
                }
            }
            catch (Exception)
            {
                /* DataColumn data1 = new DataColumn("EX", typeof(string));
                 data.Columns.Add(data1);
                 DataRow datarow = data.NewRow();
                 datarow["EX"]= ex.Message;
                 data.Rows.Add(datarow);*/
                return data;
            }
        }
        #endregion

    }
}